package com.android.Bean;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.junit.Test;

import com.android.DBTools.ADUS;
import com.android.DBTools.DBConnection;
import com.android.ObjectBean.CompanyObjectList;

import net.sf.json.JSONArray;

public class DisComListBean {
	@Test
	public String displayCompany() throws ClassNotFoundException, SQLException {
		String sql = "SELECT d.comID,count(d.comID)as length, c.ComName,d.garDisType,c.ComContent "
				+ "FROM dispiclist AS d  JOIN companylist AS c "
				+ "ON c.comID = d.comID GROUP BY d.comID ORDER BY comID";
		ResultSet rs = ADUS.selectData(sql);
		List<CompanyObjectList> comList = new ArrayList<CompanyObjectList>();
		while (rs.next()) {
			String comID = rs.getString(rs.findColumn("comID"));
			String comName = rs.getString(rs.findColumn("ComName"));
			String garDisType = rs.getString(rs.findColumn("garDisType"));
			String comContent = rs.getString(rs.findColumn("ComContent"));
			String[] garDisPath = new String[rs.getInt(rs.findColumn("length"))];
			String sqlChild = "SELECT d.garDisPath FROM dispiclist d WHERE d.comID=" + comID;
			ResultSet rsChild = ADUS.selectData(sqlChild);
			int i = 0;
			while (rsChild.next()) {
				garDisPath[i++] = rsChild.getString(rsChild.findColumn("garDisPath"));
			}
			CompanyObjectList com = new CompanyObjectList();
			com.setComID(comID);
			com.setComName(comName);
			com.setGarDisType(garDisType);
			com.setComContent(comContent);
			com.setGarDisPath(garDisPath);
			comList.add(com);
		}
		DBConnection.closeCon();
		JSONArray jsonArray = JSONArray.fromObject(comList);
		return jsonArray.toString();
	}
}
